SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC [dbo].[PReqWorkSELECT]
@iK_disc INT,
@iK_vid_zanyat INT,
@n_sem INT,
@nCode NUMERIC 
AS
SELECT 
CU.ik_upContent[idучебного плана],
Ik_ListReq[idЛиста требований],
Ik_ReqWork[idТребования к работе],
Ik_RefReq[idНазвания требования],
NameReq[Название требования],
Expdoc[Расширение файла],
SystemNumber [idЗагруженного файла],
DATELOAD [Дата загрузки файла]
FROM dbo.Student St INNER JOIN dbo.Zach Z 
ON St.nCode=Z.nCode 
INNER JOIN dbo.StudGrup SG 
ON Z.Ik_zach=SG.Ik_zach 
INNER JOIN dbo.Grup G 
ON SG.Ik_grup=G.Ik_grup 
INNER JOIN dbo.Uch_pl Up 
ON G.Ik_uch_plan=Up.ik_uch_plan 
INNER JOIN dbo.sv_disc sd 
ON Up.ik_uch_plan=sd.ik_uch_plan 
INNER JOIN dbo.discpln D 
ON sd.ik_disc=D.iK_disc 
INNER JOIN dbo.Content_UchPl CU 
ON sd.ik_disc_uch_plan=CU.ik_disc_uch_plan 
INNER JOIN dbo.Semester sem 
ON CU.n_sem=sem.n_sem 
INNER JOIN dbo.vid_zaniat vz 
ON CU.ik_vid_zanyat=vz.iK_vid_zanyat

FULL JOIN
(select SystemNumber, DATELOAD, cu.ik_upContent, Expdoc, NameReq, RW.Ik_ReqWork, rr.Ik_RefReq, LR.Ik_ListReq 
from dbo.Content_UchPl cu
INNER JOIN dbo.ReqWork RW 
ON cu.ik_upContent=RW.Ik_upContent 
INNER JOIN dbo.ListReq LR 
ON RW.Ik_ReqWork=LR.Ik_ReqWork 
INNER JOIN dbo.RefReq RR 
ON LR.Ik_RefReq=RR.Ik_RefReq 
LEFT JOIN dbo.ResultWork ResW
ON LR.Ik_ListReq=ResW.Ik_ListReq 

) b
ON CU.ik_upContent=b.ik_upContent 

WHERE 
St.nCode=@nCode AND sem.n_sem=@n_sem AND @iK_disc=D.iK_disc AND @iK_vid_zanyat=vz.iK_vid_zanyat
AND SG.Ik_prikazZach IS NOT NULL 
AND SG.ik_pricOtch IS NULL;
GO
